-- @owner: lihongji
-- @date: 2022/07/26
-- @testpoint: create table as select

--step1:建序列;expect:成功
set extra_float_digits to 0;
drop sequence if exists pop_samp_variance_seq;
create sequence pop_samp_variance_seq increment by 1 start with 10;

--step2:建表并插入数据;expect:成功
drop table if exists table0001 cascade;
create table table0001(
 col_1 integer,
 col_2 number(19,0),
 col_3 number(10,6)
) with (storage_type=ustore);

begin
 for i in 1..50 loop
  insert into table0001 values(
  i,
  pop_samp_variance_seq.nextval,
  i+445.255
  );
 end loop;
end;
/

--step3:建表;expect:成功
drop table if exists t_var_samp_defalut03;
create table t_var_samp_defalut03 with (storage_type=ustore) as select regr_avgy(col_2,col_2) as a from table0001 group by col_1,col_2,col_3;
--step4:查询;expect:成功
select * from t_var_samp_defalut03 order by 1 limit 10;
--step5:建表;expect:成功
drop table if exists t_var_samp_defalut03_1;
create table t_var_samp_defalut03_1 with (storage_type=ustore) as select regr_avgy(col_2-col_3,col_2-col_3) as a from table0001;
--step6:查询;expect:成功
select * from t_var_samp_defalut03_1 order by 1;

--step7:清理环境;expect:成功
drop table if exists t_var_samp_defalut03;
drop table if exists t_var_samp_defalut03_1;
drop sequence if exists pop_samp_variance_seq;
drop table if exists table0001 cascade;